IFI Techsolutions - Microsoft Gold Partner

Author – Moulshree S. D., Sr. Data Engineer

On-premises SQL Server does support three- or four-part names, but the Azure SQL Database does not. The Elastic Database query feature enables you to run a Transact-SQL query that spans multiple Databases in Azure SQL Database. The Database migration Assessment throwing error as “Cross-Database queries using three- or four-part names not supported in Azure SQL Database” can be eliminated using Elastic querying on Azure SQL Database. In this document, we will resolve Cross-Database Queries in Azure SQL Database. An example of an on-premises Cross-Database query is given below:

In Azure SQL Database we will execute Cross Database queries. Pre-requisites are as below:

· Login to Master Database.

· User on the Database from which you need to query from.

· Master Key on the Database on which External Tables will be created.

· Grant Select access on the Database from which you need to query from on the table.

NOTE: Grant can be given by the command given below:

GRANT SELECT ON [SCHEMA_NAME]. [TABLE_NAME]TO USER_NAME;

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azuresqldb-current#permissions-1

Execution of Cross Database queries:

Step 1: Create Database Scoped Credential

· The Database Scoped Credential is used to access external location. External location could be your another Database. It is not mapped to any Server login or Database User.

STEP 2: Create External data source

· The External Data Source will be pointed to the Database from which you will be querying the table.

· The External data source TYPE will be RDBMS for Azure SQL Database.

· Location points to the server location.

· Credential contains the Database scoped credential which was created before.

· Database name points to the Database from which the data will be queried from.

STEP 3: Create External Table

· Identity columns are not supported while creating External Table.

STEP 4: Create two-part stored procedure to load data

· The Stored procedure given below inserts into base table test13 and fetches data from the External Tables.

· Meanwhile, External Tables fetch data from another Database using its data source as connection to the other Database.

· This step involves any transformation or statements using data fetched from External Tables.

STEP 5: If source schema is altered.

· If the source table is altered to add a column, then the External Table and Destination table also needs to be altered accordingly.

· If any schema changes are been made, then the External Tables are not altered. However, they could be remade.

· Destination and source tables are base tables therefore they can be altered.

· First, we alter source and add a column as shown below:

· Next, we script External Table as “Create to” and add the column to it as shown below:

· Next, we will delete the External Table and run the SQL query to create External Table.

· We will then alter the base table and add a column on the Database where we need to insert data into and run the stored procedure to load the base table.

This resolves the error Cross Database query using three- or four-part names. Now you can use three or four part names in Elastic query on Azure SQL Database.

Elastic querying on Azure SQL Database